﻿<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System.Web.Services.Protocols" %>
<script runat="server">

    Sub Page_Load()
        Dim ConnectionString As String = ConfigurationSettings.AppSettings("strcnn")
        Dim strsql As String
        Dim myConnection As New OleDbConnection(ConnectionString)
        Dim cnn as New OleDbConnection(ConnectionString)
        Dim myCommand as New OleDbCommand
        Dim cmd as New OleDbCommand
        Dim myDataReader as OleDbDataReader
        Dim dr as OleDbDataReader
        myConnection.Open()
        cnn.Open()
        myCommand.Connection = myConnection
        cmd.Connection = cnn
    
        select(Request.Form("acao"))
        case "excluir":
            Dim primeiro as Boolean = true
            for each i as String in Request.Form
                if i <> "acao" and not i like "cancelado*" and not i like "faturado*" then
                    if primeiro then
                        strsql = "delete * from pedido where ID=" & i
                        primeiro = false
                    else
                        strsql = strsql & " or ID=" & i
                    End if
                End if
            next
            if strsql <> "" then
                myCommand.CommandText = strsql
                myCommand.ExecuteNonQuery()
            End if
        case "atualizar":
            for each i as String in Request.Form
                if i <> "acao" and i <> "RazaoSocial" and i like "cancelado*" then
                    Dim j as String = Replace(i, "cancelado", "")
                    myCommand.CommandText = "update pedido set cancelado=" & Request.Form("cancelado" & j) & ", faturado=" & Request.Form("faturado" & j) & " where ID=" & j
                    myCommand.ExecuteNonQuery()
                End if
            next
        End select
    
    
        strsql = ""
        if Request.Form("Cliente") <> "" then strsql = "cliente.razaosocial like '%" & Request.Form("Cliente") & "%'"
        if Request.Form("Cidade") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cliente.cidade like '%" & Request.Form("Cidade") & "%'"
        if Request.Form("Numero") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "(pedido.id = " & Request.Form("Numero") & " or pedido.CodigoJolitex = " & Request.Form("Numero") & ")"
        if Request.Form("Vendedor") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idvendedor=" & Request.Form("Vendedor")
        if Request.Form("Fabrica") <> "" then
		if Request.Form("Fabrica") = "0" then
			strsql = iif(strsql = "", "", strsql & " and ") & "fabrica.razaosocial like '%jolitex%'"
		else
			strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idfabrica=" & Request.Form("Fabrica")
		end if
	end if
        if Request.Form("Transportadora") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idtransportadora=" & Request.Form("Transportadora")
        dim vet
        dim data as String
        if Request.Form("De") <> "" then
            vet = Request.Form("De").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data>=#" & data & "#"
        end if
        if Request.Form("Ate") <> "" then
            vet = Request.Form("Ate").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data<=#" & data & "#"
        end if
        if Request.Form("Cancelado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cancelado=" & Request.Form("Cancelado")
        if Request.Form("Faturado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "Faturado=" & Request.Form("Faturado")
        Busca.Text = "<table width='100%' border='0' cellspacing='2' cellpadding='2'><tr bgcolor='#000080'><td><font color='#FFFFFF'><b>Data</b></font></td><td><font color='#FFFFFF'><b>Nmero</b></font></td><td><font color='#FFFFFF'><b>Cliente</b></font></td><td><font color='#FFFFFF'><b>Vendedor</b></font></td><td><font color='#FFFFFF'><b>Fbrica</b></font></td><td><font color='#FFFFFF'><b>Transportadora</b></font></td><td><font color='#FFFFFF'><b>Total</b></font></td><td><font color='#FFFFFF'><b>Faturado</b></font></td><td><font color='#FFFFFF'><b>Cancelado</b></font></td><td></td></tr>"
        Dim total as integer = 0
        dim valortotal as double = 0
        Dim cor as Boolean
	dim pecas as integer = 0
        if strsql <> "" then
           myCommand.CommandText = "select pedido.id, pedido.codigojolitex, total, cliente.id, cliente.razaosocial, vendedor.id, vendedor.nome, fabrica.id, fabrica.razaosocial, transportadora.id, transportadora.razaosocial, data, cancelado, faturado, pecas from pedido, cliente, vendedor, fabrica, transportadora where " & iif(strsql="", "", strsql & " and ") & "cliente.id=pedido.idcliente and vendedor.id=pedido.idvendedor and fabrica.id=pedido.idfabrica and transportadora.id=pedido.idtransportadora order by pedido.id desc"
            myDataReader = myCommand.ExecuteReader()
               while myDataReader.Read()
                    Busca.Text = Busca.Text & "<tr bgcolor='" & iif(cor, "#ADD8E6", "#E0FFFF") & "'><td align='center'>" & Format(myDataReader("Data"), "dd/MM/yy") & "</td><td align='center'><a href='pedidoexibiralterar.aspx?acao=exibir&id=" & myDataReader("pedido.ID") & "'>" & iif(myDataReader("codigojolitex") = 0, myDataReader("pedido.ID"), "J" & myDataReader("codigojolitex")) & "</a></td><td><a href='cliente.aspx?acao=exibir&id=" & myDataReader("cliente.id") & "'>" & myDataReader("cliente.razaosocial") & "</a></td><td><a href='vendedor.aspx?acao=exibir&id=" & myDataReader("vendedor.id") & "'>" & myDataReader("nome") & "</a></td><td><a href='fabrica.aspx?acao=exibir&id=" & myDataReader("fabrica.id") & "'>" & myDataReader("Fabrica.RazaoSocial") & "</a></td><td><a href='transportadora.aspx?acao=exibir&id=" & myDataReader("transportadora.id") & "'>" & myDataReader("transportadora.razaosocial") & "</a></td><td>R$ " & Format(myDataReader("total"), "#.00") & "</td><td align='center'><select name='faturado" & myDataReader("pedido.id") & "'><option value='true'" & iif(myDataReader("Faturado"), "selected", "") & ">Sim</option><option value='false'" & iif(not myDataReader("Faturado"), "selected", "") & ">No</option></select></td><td align='center'><select name='cancelado" & myDataReader("pedido.id") & "'><option value='true'" & iif(myDataReader("Cancelado"), "selected", "") & ">Sim</option><option value='false'" & iif(not myDataReader("Cancelado"), "selected", "") & ">No</option></select></td><td align='center'><input type='checkbox' name='" & myDataReader("pedido.ID") & "' value='true'></td></tr>"
                    cor = not cor
                    total = total + 1
                    valortotal = valortotal + myDataReader("Total")
		    pecas = pecas + myDataReader("Pecas")
            End while
            myDataReader.Close()
		myCommand.CommandText = "select sum(quantidade) as soma from produto, pedido, cliente, vendedor, fabrica, transportadora where " & iif(strsql="", "", strsql & " and ") & "cliente.id=pedido.idcliente and vendedor.id=pedido.idvendedor and fabrica.id=pedido.idfabrica and transportadora.id=pedido.idtransportadora and produto.idpedido = pedido.id"
		myDataReader = myCommand.ExecuteReader()
		myDataReader.Read()
		pecas = myDataReader("soma")
            myDataReader.Close()

       End if
        Busca.Text = Busca.Text & "</table><hr><table border='0' cellspacing='2' cellpadding='2'>"
        Busca.Text = Busca.Text & "<tr><td>Total:</td><td>" & total & "</td></tr>"
        Busca.Text = Busca.Text & "<tr><td>Pecas:</td><td>" & pecas & "</td></tr>"
        Busca.Text = Busca.Text & "<tr><td>Valor total:</td><td>R$ " & Format(valortotal, "#.00") & "</td></tr>"
        Busca.Text = Busca.Text & "<tr><td>Valor medio:</td><td>" & iif(total = 0, "", "R$ " & Format(valortotal/total, "#.00")) & "</td></tr>"
    
        strsql = ""
        if Request.Form("Cliente") <> "" then strsql = "cliente.razaosocial like '%" & Request.Form("Cliente") & "%'"
        if Request.Form("Cidade") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cliente.cidade like '%" & Request.Form("Cidade") & "%'"
        if Request.Form("Numero") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.id = " & Request.Form("Numero")
        if Request.Form("Fabrica") <> "" then
			if Request.Form("Fabrica") = "0" then
				strsql = iif(strsql = "", "", strsql & " and ") & "fabrica.razaosocial like '%jolitex%'"
			else
				strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idfabrica=" & Request.Form("Fabrica")
			end if
		end if
        if Request.Form("Transportadora") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idtransportadora=" & Request.Form("Transportadora")
        if Request.Form("De") <> "" then
            vet = Request.Form("De").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data>=#" & data & "#"
        end if
        if Request.Form("Ate") <> "" then
            vet = Request.Form("Ate").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data<=#" & data & "#"
        end if
        if Request.Form("Cancelado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cancelado=" & Request.Form("Cancelado")
        if Request.Form("Faturado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "Faturado=" & Request.Form("Faturado")
        myCommand.CommandText = "select sum(total) as total from pedido, cliente, fabrica, transportadora where " & iif(strsql="", "", strsql & " and ") & "cliente.id=pedido.idcliente and fabrica.id=pedido.idfabrica and transportadora.id=pedido.idtransportadora"
        myDataReader = myCommand.ExecuteReader()
        myDataReader.Read()
        try
            Busca.Text = Busca.Text & "<tr><td>Porcentagem:</td><td>" & iif(myDataReader("total")=0, "", Format(valortotal*100/myDataReader("total"), "#.00") & "%") & "</td></tr>"
        catch
            Busca.Text = Busca.Text & "<tr><td>Porcentagem:</td><td></td></tr>"
        end try
        myDataReader.Close()
    
        Busca.Text = Busca.Text & "</table>"
    
        myCommand.CommandText = "select id, nome from vendedor order by nome asc"
        myDataReader = myCommand.ExecuteReader()
        Vendedor.Text = "<select name='Vendedor'><option value=''></option>"
        while myDataReader.Read()
            Vendedor.Text = Vendedor.Text & "<option value='" & myDataReader("id") & "'>" & myDataReader("nome") & "</option>"
        end while
	Vendedor.Text = Vendedor.Text & "</select>"
        myDataReader.Close()
    
        myCommand.CommandText = "select id, razaosocial from Fabrica order by razaosocial asc"
        myDataReader = myCommand.ExecuteReader()
        Fabrica.Text = "<select name='Fabrica'><option value=''></option><option value='0'>** Jolitex **</option>"
        while myDataReader.Read()
            Fabrica.Text = Fabrica.Text & "<option value='" & myDataReader("id") & "'>" & myDataReader("razaosocial") & "</option>"
        end while
	Fabrica.Text = Fabrica.Text & "</select>"
        myDataReader.Close()
    
        myCommand.CommandText = "select id, razaosocial from Transportadora order by razaosocial asc"
        myDataReader = myCommand.ExecuteReader()
        Transportadora.Text = "<select name='Transportadora'><option value=''></option>"
        while myDataReader.Read()
            Transportadora.Text = Transportadora.Text & "<option value='" & myDataReader("id") & "'>" & myDataReader("razaosocial") & "</option>"
        end while
	Transportadora.Text = Transportadora.Text & "</select>"
        myDataReader.Close()
    
        myConnection.Close()
    End Sub
    
    Sub Imprimir_Click(sender As Object, e As EventArgs)
        Dim strsql As String = ""
        if Request.Form("Cliente") <> "" then strsql = "cliente.razaosocial like %27%25" & Request.Form("Cliente") & "%25%27"
        if Request.Form("Cidade") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cliente.cidade like '%" & Request.Form("Cidade") & "%'"
        if Request.Form("Numero") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "(pedido.id = " & Request.Form("Numero") & " or pedido.CodigoJolitex = " & Request.Form("Numero") & ")"
        if Request.Form("Vendedor") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idvendedor=" & Request.Form("Vendedor")
        if Request.Form("Fabrica") <> "" then
		if Request.Form("Fabrica") = "0" then
			strsql = iif(strsql = "", "", strsql & " and ") & "fabrica.razaosocial like '%jolitex%'"
		else
			strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idfabrica=" & Request.Form("Fabrica")
		end if
	end if
        if Request.Form("Transportadora") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idtransportadora=" & Request.Form("Transportadora")
        dim vet
        dim data as String
        if Request.Form("De") <> "" then
            vet = Request.Form("De").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data>=%23" & data & "%23"
        end if
        if Request.Form("Ate") <> "" then
            vet = Request.Form("Ate").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data<=%23" & data & "%23"
        end if
        if Request.Form("Cancelado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cancelado=" & Request.Form("Cancelado")
        if Request.Form("Faturado") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "Faturado=" & Request.Form("Faturado")
        strsql = "SELECT Pedido.Data, Pedido.ID, Cliente.RazaoSocial, Pedido.CondicaoPagamento, Produto.Descricao, Produto.Quantidade, [Produto]![Quantidade]*[Produto]![PrecoUnitario] AS Total, Vendedor.Sigla, Pedido.Total from fabrica, Vendedor INNER JOIN ((Cliente INNER JOIN Pedido ON Cliente.ID = Pedido.IDCliente) INNER JOIN Produto ON Pedido.ID = Produto.IDPedido) ON Vendedor.ID = Pedido.IDVendedor " & iif(strsql="", "", "where " & strsql) & " order by Pedido.ID"
    
        'Response.Write(strsql)
        'Response.End
        Response.Redirect("pedidolistaimprimir.aspx?strsql=" & strsql)
    End Sub
    
    Sub Email_Click(sender As Object, e As EventArgs)
        dim strsql as string = ""
        if Request.Form("Cliente") <> "" then strsql = strsql & " cliente.razaosocial like %27%25" & Request.Form("Cliente") & "%25%27"
        if Request.Form("Cidade") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "cliente.cidade like '%" & Request.Form("Cidade") & "%'"
        if Request.Form("Numero") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.id = " & Request.Form("Numero") & "or pedido.codigojolitex = " & Request.Form("Numero")
        if Request.Form("Vendedor") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idvendedor=" & Request.Form("Vendedor")
        if Request.Form("Fabrica") <> "" then
		if Request.Form("Fabrica") = "0" then
			strsql = iif(strsql = "", "", strsql & " and ") & "fabrica.razaosocial like '%jolitex%'"
		else
			strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idfabrica=" & Request.Form("Fabrica")
		end if
	end if
        if Request.Form("Transportadora") <> "" then strsql = iif(strsql = "", "", strsql & " and ") & "pedido.idtransportadora=" & Request.Form("Transportadora")
        dim vet
        dim data as String
        if Request.Form("De") <> "" then
            vet = Request.Form("De").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data>=%23" & data & "%23"
        end if
        if Request.Form("Ate") <> "" then
            vet = Request.Form("Ate").Split("/")
            data = vet(1) & "/" & vet(0) & "/" & vet(2)
            strsql = iif(strsql = "", "", strsql & " and ") & "data<=%23" & data & "%23"
        end if
    
        strsql = "SELECT Pedido.*, Cliente.*, Fabrica.ID, Fabrica.RazaoSocial, Fabrica.Email, Transportadora.ID, Transportadora.RazaoSocial, Vendedor.ID, Vendedor.Sigla FROM Vendedor INNER JOIN (Transportadora INNER JOIN (Fabrica INNER JOIN (Cliente INNER JOIN Pedido ON Cliente.ID = Pedido.IDCliente) ON Fabrica.ID = Pedido.IDFabrica) ON Transportadora.ID = Pedido.IDTransportadora) ON Vendedor.ID = Pedido.IDVendedor WHERE " & strsql
    
        'Response.Write(strsql)
        'Response.End
        Response.Redirect("pedidoemail.aspx?strsql=" & strsql)
    End Sub

</script>
<html>
<head>
    <script language="vbscript">
    sub confirma(acao)
        if acao = "excluir" then
            document.lista.acao.value = "excluir"
            if msgbox("Confirmar excluso?", vbYesNo) = vbYes then document.lista.submit()
        else
            document.lista.acao.value = "atualizar"
            if msgbox("Confirmar atualizao?", vbYesNo) = vbYes then document.lista.submit()
        end if
    end sub
</script>
</head>
<body style="FONT-FAMILY: arial">
    <table bordercolor="navajowhite" cellspacing="0" cellpadding="2" width="950" align="center" bgcolor="floralwhite">
        <tbody>
            <tr>
                <td valign="center" align="middle" bgcolor="#ffdead" colspan="2">
                    <h2>Pedido 
                    </h2>
                </td>
            </tr>
            <tr>
                <td valign="top">
                    <table border="0">
                        <tbody>
                            <tr>
                                <td>
                                    <h3>Pesquisar: 
                                    </h3>
                                    <form method="post" runat="server">
                                        <table border="0">
                                            <tbody>
                                                <tr>
                                                    <td>
                                                        Nmero:</td>
                                                    <td>
                                                        <input name="Numero" /></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Cliente:</td>
                                                    <td>
                                                        <input name="Cliente" /></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Data:</td>
                                                    <td>
                                                        <input maxlength="8" size="8" name="De" />&nbsp;at &nbsp;<input type="text" maxlength="8" size="8" name="Ate" /></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Vendedor:</td>
                                                    <td>
                                                        <asp:Label id="Vendedor" runat="server"></asp:Label></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Fbrica:</td>
                                                    <td>
                                                        <asp:Label id="Fabrica" runat="server"></asp:Label></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Transportadora:</td>
                                                    <td>
                                                        <asp:Label id="Transportadora" runat="server"></asp:Label></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Cidade:</td>
                                                    <td>
                                                        <input name="Cidade" /></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Cancelado:</td>
                                                    <td>
                                                        <select name="Cancelado">
                                                            <option value="" selected="selected">
                                                            </option>
                                                            <option value="true">Sim</option>
                                                            <option value="false">No</option>
                                                        </select></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        Faturado:</td>
                                                    <td>
                                                        <select name="Faturado">
                                                            <option value="" selected="selected">
                                                            </option>
                                                            <option value="true">Sim</option>
                                                            <option value="false">No</option>
                                                        </select></td>
                                                </tr>
                                                <tr>
                                                    <td>
                                                        <input type="submit" value="Pesquisar" /></td>
                                                    <td align="middle">
                                                        <asp:Button id="Email" onclick="Email_Click" runat="server" Text="Enviar por e-mail"></asp:Button>
                                                    </td>
                                                    <td align="right">
                                                        <asp:Button id="Imprimir" onclick="Imprimir_Click" runat="server" Text="Imprimir"></asp:Button>
                                                    </td>
                                                </tr>
                                            </tbody>
                                        </table>
                                    </form>
                                </td>
                            </tr>
                            <tr>
                                <td align="right">
                                    <form name="lista" method="post">
                                        <input type="hidden" name="acao" />
                                        <asp:Label id="Busca" runat="server">Busca</asp:Label> 
                                        <hr />
                                        <input onclick="vbscript:confirma('atualizar')" type="button" value="Atualizar" />
                                        <input onclick="vbscript:confirma('excluir')" type="button" value="Excluir" />
                                    </form>
                                </td>
                            </tr>
                        </tbody>
                    </table>
                </td>
            </tr>
        </tbody>
    </table>
</body>
</html>
